﻿using System.Data.SQLite;

namespace RobosimAPI.Class
{
	public class Score
	{
		/*
		CREATE TABLE map_test1 (
		user_id    INTEGER UNIQUE,
		score      NUMERIC DEFAULT ( -1),
		is_entered INTEGER,
		sub_limit     INTEGER DEFAULT (1),
		[limit]    INTEGER DEFAULT ( -1),
		sub_time   INTEGER
		round_id   INTEGER
		final_score NUMERIC,
		local_time  TEXT,
		duration    NUMERIC,
		use_robot   TEXT
		program     BLOB,
		robot       BLOB
		);
		 */
		string? table_name;
		public int user_id;
		public decimal score;
		public int is_entered;
		public int sub_limit;
		public int limit;
		public int sub_time;
		public bool is_valid;
		public int round_id;
		public decimal final_score;
		public string? local_time;
		public decimal duration;
		public string? use_robot;
		public byte[]? program;
		public byte[]? robot;
		public Score(string table_name,int user_id,int round_id)
		{
			this.table_name=table_name;
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"SELECT user_id, score, is_entered, sub_limit, [limit], sub_time, round_id, final_score, local_time, duration, use_robot FROM {table_name} WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					using (var reader = cmd.ExecuteReader())
					{
						if (reader.Read())
						{
							this.user_id =reader.GetInt32(0);
							this.score = reader.GetDecimal(1);
							this.is_entered = reader.GetInt32(2);
							this.sub_limit = reader.GetInt32(3);
							this.limit = reader.GetInt32(4);
							this.sub_time = reader.GetInt32(5);
							this.round_id = reader.GetInt32(6);
							this.final_score = reader.GetDecimal(7);
							this.local_time = reader.IsDBNull(8)? null : reader.GetString(8);
							this.duration = reader.GetDecimal(9);
							this.use_robot = reader.IsDBNull(10)? null : reader.GetString(10);
							this.is_valid = true;
						}
						else
						{
							this.is_valid = false;
						}
					}
				}
				conn.Close();
			}
		}
		public void Update()
		{
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"UPDATE {table_name} SET score = @score, is_entered = @is_entered, sub_limit = @submit, [limit] = @limit, sub_time = @sub_time, final_score = @final_score, local_time = @local_time, duration = @duration, use_robot = @use_robot WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@score", score);
					cmd.Parameters.AddWithValue("@is_entered", is_entered);
					cmd.Parameters.AddWithValue("@submit", sub_limit);
					cmd.Parameters.AddWithValue("@limit", limit);
					cmd.Parameters.AddWithValue("@sub_time", sub_time);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					cmd.Parameters.AddWithValue("@final_score", final_score);
					cmd.Parameters.AddWithValue("@local_time", local_time);
					cmd.Parameters.AddWithValue("@duration", duration);
					cmd.Parameters.AddWithValue("@use_robot", use_robot);
					cmd.ExecuteNonQuery();
				}
				conn.Close();
			}	
		}
		public void GetProgram()
		{
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"SELECT program FROM {table_name} WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					using (var reader = cmd.ExecuteReader())
					{
						if (reader.Read())
						{
							program = (byte[])reader["program"];
						}
					}
				}
				conn.Close();
			}
		}
		public void GetRobot()
		{
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"SELECT robot FROM {table_name} WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					using (var reader = cmd.ExecuteReader())
					{
						if (reader.Read())
						{
							robot = (byte[])reader["robot"];
						}
					}
				}
				conn.Close();
			}
		}	
		public void SetProgram(byte[] program)
		{
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"UPDATE {table_name} SET program = @program WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					cmd.Parameters.AddWithValue("@program", program);
					cmd.ExecuteNonQuery();
				}
				conn.Close();
			}
		}
		public void SetRobot(byte[] robot)
		{
			using (var conn = new SQLiteConnection(Account.DB_CONNECTION_STRING))
			{
				conn.Open();
				using (var cmd = new SQLiteCommand(conn))
				{
					cmd.CommandText = $"UPDATE {table_name} SET robot = @robot WHERE user_id = @user_id AND round_id = @round_id";
					cmd.Parameters.AddWithValue("@user_id", user_id);
					cmd.Parameters.AddWithValue("@round_id", round_id);
					cmd.Parameters.AddWithValue("@robot", robot);
					cmd.ExecuteNonQuery();
				}
				conn.Close();
			}
		}
	}
}
